Release 10.1A: OpenEdge Data Management:
SQL Development

Updating index statistics

The UPDATE STATISTICS command uses the following syntax:

Syntax

UPDATE ( [ TABLE | INDEX | [ ALL ] COLUMN ] STATISTICS [ AND ] ) ... [ FOR table_name ]

The following example demonstrates the use of the UPDATE STATISTICS statement for a single table:

UPDATE INDEX STATISTICS FOR Employee;

The following example updates statistics for indexes and columns for a single employee:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND COLUMN STATISTICS FOR Employee ;

To create the new index statistics for all tables in a database, simply use the statement shown in Example 10–3.

Example 10–3: UPDATE INDEX STATISTICS statement

UPDATE INDEX STATISTICS ;

Notes: To create the new index statistics, SQL makes one pass over each index, reading every index entry and counting unique values. This is usually a CPU-intensive operation. When a table has many indexes, this operation can take quite a bit more time than the default UPDATE STATISTICS.

UPDATE STATISTICS does not lock user data. It only locks the output statistics rows (and also acquires a shared lock on the schema). This means that user-level transactions can freely run concurrently with UPDATE STATISTICS.